package cn.com.checne.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import cn.com.checne.model.domain.JobInfo;
import cn.com.checne.model.domain.LanguageInfo;
import cn.com.checne.model.domain.ResumeCertificateInfo;
import cn.com.checne.model.domain.ResumeEducationInfo;
import cn.com.checne.model.domain.ResumeFamilyInfo;
import cn.com.checne.model.domain.ResumeInfo;
import cn.com.checne.model.domain.ResumeTrainInfo;
import cn.com.checne.model.domain.ResumeWorkInfo;
import cn.com.checne.model.dto.ResumeQuery;


@Repository
public class ResumeInfoDao {
	protected static final Logger log = Logger.getLogger(ResumeInfoDao.class);

	/**
	 * create
	 * @param cate
	 * @return
	 */
	public boolean create(ResumeInfo resume) {
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			
			String resumeSQL = "insert into tbl_resume(lg_id_fk,job_id,resume_id,name,birthday,gender,politicsStatus,nation,marriageStatus,bearStatus,height," +
					"image,email,areaCode,telephone,performance,rewardsPunishment,unHandleThing,careerPlanning,personalHobbies,resume_time)" +
					" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(resumeSQL);
			int i = 0 ;
			pstmt.setInt(++i, resume.getLanguageId());
			pstmt.setInt(++i, resume.getJobId());
			pstmt.setString(++i, resume.getResumeId());
			pstmt.setString(++i, resume.getName());
			pstmt.setString(++i, resume.getBirthday());
			pstmt.setInt(++i, resume.getGender());
			pstmt.setInt(++i, resume.getPoliticsStatus());
			pstmt.setString(++i, resume.getNation());
			pstmt.setInt(++i, resume.getMarriageStatus());
			pstmt.setString(++i, resume.getBearStatus());
			pstmt.setInt(++i, resume.getHeight());
			
			pstmt.setString(++i, resume.getImage());
			pstmt.setString(++i, resume.getEmail());
			pstmt.setString(++i, resume.getAreaCode());
			pstmt.setString(++i, resume.getTelephone());
			pstmt.setString(++i, resume.getPerformance());
			pstmt.setString(++i, resume.getRewardsPunishment());
			pstmt.setInt(++i, resume.getUnHandleThing());
			pstmt.setString(++i, resume.getCareerPlanning());
			pstmt.setString(++i, resume.getPersonalHobbies());
			pstmt.setTimestamp(++i,new java.sql.Timestamp(new java.util.Date().getTime()));
			pstmt.execute();
			
			//教育经历

/*			CREATE TABLE `tbl_resume_education` (
			  `resume_id` varchar(32) NOT NULL COMMENT '简历主键',
			  `id` int(11) NOT NULL auto_increment,
			  `start` varchar(100) default NULL COMMENT '开始时间',
			  `end` varchar(100) default NULL COMMENT '结束时间',
			  `school` varchar(100) default NULL COMMENT '毕业学校',
			  `profession` varchar(100) default NULL COMMENT '业专',
			  `Background` varchar(100) default NULL COMMENT '历学',
			  `egree` tinyint(100) default NULL COMMENT '学位',
			  `category` varchar(200) default NULL COMMENT '学习形式',
			  PRIMARY KEY  (`id`)
			) ENGINE=InnoDB DEFAULT CHARSET=utf8;*/
			String edSQL = "insert into tbl_resume_education(resume_id,start,end,school,profession,Background,egree,category)"
							+ " values(?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(edSQL);
			for(ResumeEducationInfo ed : resume.getResumeEducationList()){
				i = 0 ;
				pstmt.setString(++i, ed.getResumeId());
				pstmt.setString(++i, ed.getStart());
				pstmt.setString(++i, ed.getEnd());
				pstmt.setString(++i, ed.getSchool());
				pstmt.setString(++i, ed.getProfession());
				pstmt.setString(++i, ed.getBackground());
				pstmt.setString(++i, ed.getEgree());
				pstmt.setString(++i, ed.getCategory());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			
			//工作经历
//			CREATE TABLE `tbl_resume_work` (
//					  `resume_id` varchar(32) NOT NULL COMMENT '简历主键',
//					  `id` int(11) NOT NULL auto_increment,
//					  `start` varchar(100) default NULL COMMENT '开始时间',
//					  `end` varchar(100) default NULL COMMENT '结束时间',
//					  `company` varchar(200) default NULL COMMENT '工作单位',
//					  `station` varchar(100) default NULL COMMENT '工作岗位',
//					  `provePerson` varchar(100) default NULL COMMENT '证明人',
//					  `contact` varchar(200) default NULL COMMENT '联系方式',
//					  `responsibility` text COMMENT '职责',
//					  `leaveReason` text COMMENT '离职原因',
//					  PRIMARY KEY  (`id`)
//					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			String wkSQL = "insert into tbl_resume_work(resume_id,start,end,company,station,provePerson,contact,responsibility,leaveReason)" +
							" values(?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(wkSQL);
			for(ResumeWorkInfo wk : resume.getResumeWorkList()){
				i = 0 ;
				pstmt.setString(++i, wk.getResumeId());
				pstmt.setString(++i, wk.getStart());
				pstmt.setString(++i, wk.getEnd());
				pstmt.setString(++i, wk.getCompany());
				pstmt.setString(++i, wk.getStation());
				pstmt.setString(++i, wk.getProvePerson());
				pstmt.setString(++i, wk.getContact());
				pstmt.setString(++i, wk.getResponsibility());
				pstmt.setString(++i, wk.getLeaveReason());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			//证书
//			CREATE TABLE `tbl_resume_certificate` (
//					  `resume_id` varchar(32) NOT NULL COMMENT '简历主键',
//					  `id` int(11) NOT NULL auto_increment,
//					  `time` varchar(100) default NULL COMMENT '时间',
//					  `company` varchar(200) default NULL COMMENT '机构',
//					  `name` varchar(100) default NULL COMMENT '名称',
//					  PRIMARY KEY  (`id`)
//					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			String cfSQL = "insert into tbl_resume_certificate(resume_id,cftime,company,name)" +
							" values(?,?,?,?)";
			pstmt = conn.prepareStatement(cfSQL);
			for(ResumeCertificateInfo cf : resume.getResumeCertificaeList()){
				i = 0 ;
				pstmt.setString(++i, cf.getResumeId());
				pstmt.setString(++i, cf.getTime());
				pstmt.setString(++i, cf.getCompany());
				pstmt.setString(++i, cf.getName());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			//培训机构
//			CREATE TABLE `tbl_resume_train` (
//					  `resume_id` varchar(32) NOT NULL COMMENT '简历主键',
//					  `id` int(11) NOT NULL auto_increment,
//					  `start` varchar(100) default NULL COMMENT '时间',
//					  `end` varchar(100) default NULL COMMENT '时间',
//					  `company` varchar(200) default NULL COMMENT '机构',
//					  `content` varchar(500) default NULL COMMENT '内容',
//					  PRIMARY KEY  (`id`)
//					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			String trSQL = "insert into tbl_resume_train(resume_id,start,end,company,content)" +
						" values(?,?,?,?,?)";
			pstmt = conn.prepareStatement(trSQL);
			for(ResumeTrainInfo tr : resume.getResumeTrainList()){
				i = 0 ;
				pstmt.setString(++i, tr.getResumeId());
				pstmt.setString(++i, tr.getStart());
				pstmt.setString(++i, tr.getEnd());
				pstmt.setString(++i, tr.getCompany());
				pstmt.setString(++i, tr.getContent());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			
			//家庭情况
//			CREATE TABLE `tbl_resume_family` (
//					  `resume_id` varchar(32) NOT NULL COMMENT '简历主键',
//					  `id` int(11) NOT NULL auto_increment,
//					  `name` varchar(100) default NULL COMMENT '',
//					  `relation` varchar(100) default NULL COMMENT '关系',
//					  `company` varchar(200) default NULL COMMENT '工作单位',
//					  `station` varchar(200) default NULL COMMENT '工作岗位',
//					  PRIMARY KEY  (`id`)
//					) ENGINE=InnoDB DEFAULT CHARSET=utf8;
			String faSQL = "insert into tbl_resume_family(resume_id,name,relation,company,station)" +
			" values(?,?,?,?,?)";
			pstmt = conn.prepareStatement(faSQL);
			for(ResumeFamilyInfo fa : resume.getResumeFamilyList()){
				i = 0 ;
				pstmt.setString(++i, fa.getResumeId());
				pstmt.setString(++i, fa.getName());
				pstmt.setString(++i, fa.getRelation());
				pstmt.setString(++i, fa.getCompany());
				pstmt.setString(++i, fa.getStation());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			pstmt.clearBatch();
			
			conn.commit();
			pstmt.close();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result;
	}

	/**
	 * 
	 * @param cateId
	 * @return
	 */
	public List<ResumeInfo> getResumeList(ResumeQuery query,int start,int num) {
		List<ResumeInfo> list = new ArrayList<ResumeInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_resume where 1=1 ";
			if(query.getLanguageId() > 0 ){
				sql += " and lg_id_fk = ? " ;
			}
			sql += " order by resume_time desc limit ?,?" ;
			int i = 0 ;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if(query.getLanguageId() > 0 ){
				pstmt.setInt(++i, query.getLanguageId());
			}
			pstmt.setInt(++i, start);
			pstmt.setInt(++i, num);
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list = getResult(rs);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public int getResumeCount(ResumeQuery query) {
		int total = 0 ;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as total from tbl_resume where 1=1 ";
			if(query.getLanguageId() > 0 ){
				sql += " and lg_id_fk = ? " ;
			}
			int i = 0 ;
			PreparedStatement pstmt = conn.prepareStatement(sql);
			if(query.getLanguageId() > 0 ){
				pstmt.setInt(++i, query.getLanguageId());
			}
			log.info("[SQL]" + pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				total = rs.getInt("total");
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return total;
	}
	
	public ResumeInfo getResumeById(String resumeId) {
		ResumeInfo resume = new ResumeInfo();
		try {
			String sql = "select * from tbl_resume where resume_id = ? " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			List<ResumeInfo> list = getResult(rs);
			resume = list != null && list.size() > 0 ? list.get(0) : resume ;
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resume;
	}
	
	
	/**
	 * get rseult from resultSet
	 * @param rs
	 * @return
	 */
	public List<ResumeInfo> getResult(ResultSet rs) {
		List<ResumeInfo> list = new ArrayList<ResumeInfo>();
		try {
			ResumeInfo re = null ;
			while(rs.next()) {
				re = new ResumeInfo();
				re.setLanguageId(rs.getInt("lg_id_fk"));
				LanguageInfo languageInfo = new LanguageInfoDao().getLanguageInfoById(re.getLanguageId());
				re.setLanguageInfo(languageInfo);
				
				re.setJobId(rs.getInt("job_id"));
				JobInfo jobInfo = new JobInfoDao().getJobById(re.getJobId());
				re.setJobInfo(jobInfo);
				
				re.setResumeId(rs.getString("resume_id"));
				re.setName(rs.getString("name"));
				re.setBirthday(rs.getString("birthday"));
				re.setGender(rs.getInt("gender"));
				re.setPoliticsStatus(rs.getInt("politicsStatus"));
				re.setNation(rs.getString("nation"));
				re.setMarriageStatus(rs.getInt("marriageStatus"));
				re.setBearStatus(rs.getString("bearStatus"));
				re.setHeight(rs.getInt("height"));
				re.setImage(rs.getString("image"));
				re.setEmail(rs.getString("email"));
				re.setAreaCode(rs.getString("areaCode"));
				re.setTelephone(rs.getString("telephone"));
				
				re.setPerformance(rs.getString("performance"));
				re.setRewardsPunishment(rs.getString("rewardsPunishment"));
				re.setUnHandleThing(rs.getInt("unHandleThing"));
				re.setCareerPlanning(rs.getString("careerPlanning"));
				re.setPersonalHobbies(rs.getString("personalHobbies"));

				re.setResumeTime(rs.getTimestamp("resume_time"));

				re.setResumeCertificaeList(getCertificateList(re.getResumeId()));
				re.setResumeEducationList(getEducationList(re.getResumeId()));
				re.setResumeFamilyList(getFamilyList(re.getResumeId()));
				re.setResumeTrainList(getTrainList(re.getResumeId()));
				re.setResumeWorkList(getWorkList(re.getResumeId()));
				
				list.add(re);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//简历--证书
	public List<ResumeCertificateInfo> getCertificateList(String resumeId) {
		List<ResumeCertificateInfo> list = new ArrayList<ResumeCertificateInfo>();
		ResumeCertificateInfo cf = null ;
		try {
			String sql = "select * from tbl_resume_certificate where resume_id = ? order by id desc " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				cf =  new ResumeCertificateInfo();
				cf.setResumeId(rs.getString("resume_id"));
				cf.setId(rs.getInt("id"));
				cf.setTime(rs.getString("cftime"));
				cf.setCompany(rs.getString("company"));
				cf.setName(rs.getString("name"));
				list.add(cf);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//简历--教育
	public List<ResumeEducationInfo> getEducationList(String resumeId) {
		List<ResumeEducationInfo> list = new ArrayList<ResumeEducationInfo>();
		ResumeEducationInfo ed = null ;
		try {
			String sql = "select * from tbl_resume_education where resume_id = ? order by id desc " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				ed =  new ResumeEducationInfo();
				ed.setResumeId(rs.getString("resume_id"));
				ed.setId(rs.getInt("id"));
				ed.setStart(rs.getString("start"));
				ed.setEnd(rs.getString("end"));
				ed.setSchool(rs.getString("school"));
				ed.setProfession(rs.getString("profession"));
				ed.setBackground(rs.getString("Background"));
				ed.setEgree(rs.getString("egree"));
				ed.setCategory(rs.getString("category"));
				list.add(ed);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//简历--家庭
	public List<ResumeFamilyInfo> getFamilyList(String resumeId) {
		List<ResumeFamilyInfo> list = new ArrayList<ResumeFamilyInfo>();
		ResumeFamilyInfo fa = null ;
		try {
			String sql = "select * from tbl_resume_family where resume_id = ? order by id desc " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				fa =  new ResumeFamilyInfo();
				fa.setResumeId(rs.getString("resume_id"));
				fa.setId(rs.getInt("id"));
				fa.setName(rs.getString("name"));
				fa.setRelation(rs.getString("relation"));
				fa.setCompany(rs.getString("company"));
				fa.setStation(rs.getString("station"));
				list.add(fa);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//简历--培训
	public List<ResumeTrainInfo> getTrainList(String resumeId) {
		List<ResumeTrainInfo> list = new ArrayList<ResumeTrainInfo>();
		ResumeTrainInfo fa = null ;
		try {
			String sql = "select * from tbl_resume_train where resume_id = ? order by id desc " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				fa =  new ResumeTrainInfo();
				fa.setResumeId(rs.getString("resume_id"));
				fa.setId(rs.getInt("id"));
				fa.setStart(rs.getString("start"));
				fa.setEnd(rs.getString("end"));
				fa.setCompany(rs.getString("company"));
				fa.setContent(rs.getString("content"));
				list.add(fa);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	//简历--工作经历
	public List<ResumeWorkInfo> getWorkList(String resumeId) {
		List<ResumeWorkInfo> list = new ArrayList<ResumeWorkInfo>();
		ResumeWorkInfo fa = null ;
		try {
			String sql = "select * from tbl_resume_work where resume_id = ? order by id desc " ;
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, resumeId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				fa =  new ResumeWorkInfo();
				fa.setResumeId(rs.getString("resume_id"));
				fa.setId(rs.getInt("id"));
				fa.setStart(rs.getString("start"));
				fa.setEnd(rs.getString("end"));
				fa.setCompany(rs.getString("company"));
				fa.setStation(rs.getString("station"));
				fa.setProvePerson(rs.getString("provePerson"));
				fa.setContact(rs.getString("contact"));
				fa.setResponsibility(rs.getString("responsibility"));
				fa.setLeaveReason(rs.getString("leaveReason"));
				list.add(fa);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 删除简历
	 * @param id
	 * @return
	 */
	public boolean delete(String resumeId){
		boolean result = false;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			String sqls [] = new String[6];
			sqls [0] = "delete from  tbl_resume_work where resume_id = '" + resumeId + "'";
			sqls [1] = "delete from  tbl_resume_train where resume_id = '" + resumeId + "'";
			sqls [2] = "delete from  tbl_resume_family where resume_id = '" + resumeId + "'";
			sqls [3] = "delete from  tbl_resume_education where resume_id = '" + resumeId + "'";
			sqls [4] = "delete from  tbl_resume_certificate where resume_id = '" + resumeId + "'";
			sqls [5] = "delete from  tbl_resume where resume_id = '" + resumeId + "'";
			
			Statement stmt = conn.createStatement();
			stmt.addBatch(sqls[0]);
			stmt.addBatch(sqls[1]);
			stmt.addBatch(sqls[2]);
			stmt.addBatch(sqls[3]);
			stmt.addBatch(sqls[4]);
			stmt.addBatch(sqls[5]);
			stmt.executeBatch();
			
			conn.commit();
			conn.close();
			result = true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false ;
		}
		return result ;
	}
}